create or replace package body mapping_pkg is
procedure truncate_tab(p_tab_name in varchar2) is
begin
    execute immediate 'truncate table '||p_tab_name;
end;
function get_last_load_date return date is
begin
    return sysdate - 1;
end get_last_load_date;
procedure load_stage is
    v_last_load_date date;
    v_current_load_date date;
begin
    v_last_load_date := get_last_load_date;
    v_current_load_date := sysdate;
    truncate_tab('tmp_header');
    truncate_tab('tmp_attributes');
    
    insert into tmp_header
    (
        tmp_id,
        tmp_create_date,
        tmp_last_modified
    )
    select 
    id,
    create_date,
    last_modified
    from header 
    where
        last_modified >= v_last_load_date and last_modifed <= v_current_load_date;
        
    insert into tmp_attributes
    (
        tmp_ATTR_ID,
        tmp_HEADER_ID,
        tmp_ATTR_TYPE,
        tmp_ATTR_VALUE
    )
    select /*+ USE_NL(attr tmp_h)*/
    ATTR_ID,
    HEADER_ID,
    ATTR_TYPE,
    ATTR_VALUE
    from 
        attributes attr, tmp_header tmp_h
    where
        ATTR.header_ID = TMP_H.TMP_ID;
         
end load_stage;
procedure resolve_bads is
begin
    null;
end resolve_bads;
procedure load_final is
begin
    null;
end load_final;
end;
/